package com.woniuxy01;

import java.sql.*;
import java.util.Scanner;

/**
 * @author 作者
 * @create 2025-11-18 9:44
 */
public class Test01 {
    public static void main(String[] args) throws Exception {
        Scanner scanner=new Scanner(System.in);
        System.out.println("输入用户名");
        String sname = scanner.nextLine();
        System.out.println("输入密码");
        String pwd = scanner.nextLine();
        String className="com.mysql.cj.jdbc.Driver";
        String url="jdbc:mysql:///db1";
        String user="root";
        String password="123";
        Class.forName(className);
        Connection connection = DriverManager.getConnection(url, user, password);
        String sql="SELECT * FROM stu WHERE sname=? AND pwd=?";
        PreparedStatement statement = connection.prepareStatement(sql);
        statement.setObject(1,sname);
        statement.setObject(2,pwd);
        ResultSet resultSet = statement.executeQuery();
        if(resultSet.next()){
            System.out.println("登录成功");
        }else{
            System.out.println("登录失败");
        }
        resultSet.close();
        statement.close();
        connection.close();
    }

    private static void test06() throws ClassNotFoundException, SQLException {
        Scanner scanner=new Scanner(System.in);
        System.out.println("输入用户名");
        String sname = scanner.nextLine();
        System.out.println("输入密码");
        String pwd = scanner.nextLine();
        String className="com.mysql.cj.jdbc.Driver";
        String url="jdbc:mysql:///db1";
        String user="root";
        String password="123";
        Class.forName(className);
        Connection connection = DriverManager.getConnection(url, user, password);
        String sql="select * from stu where sname='"+sname+"' and pwd='"+pwd+"'";
        Statement statement = connection.createStatement();
        ResultSet resultSet = statement.executeQuery(sql);
        if(resultSet.next()){
            System.out.println("登录成功");
        }else{
            System.out.println("登录失败");
        }
        resultSet.close();
        statement.close();
        connection.close();
    }

    private static void test05() throws ClassNotFoundException, SQLException {
        Scanner scanner=new Scanner(System.in);
        System.out.println("输入你要查询学生的学号");
        int sid = scanner.nextInt();
        String className="com.mysql.cj.jdbc.Driver";
        String url="jdbc:mysql:///db1";
        String user="root";
        String password="123";
        Class.forName(className);
        Connection connection = DriverManager.getConnection(url, user, password);
        String sql="select * from stu where sid="+sid;
        Statement statement = connection.createStatement();
        ResultSet resultSet = statement.executeQuery(sql);
        //next()方法，判断是否存在下一条数据，存在，返回true,并且会读出下一条数据
        if(resultSet.next()){
            //通过下标获取某列数据，JDBC下标是从1开始的
            System.out.println(resultSet.getInt(1));
            ///通过列名获取某列数据
            System.out.println(resultSet.getString("sname"));
            System.out.println(resultSet.getObject(3));
            System.out.println("-------------");
        }
        resultSet.close();
        statement.close();
        connection.close();
    }

    private static void test04() throws ClassNotFoundException, SQLException {
        String className="com.mysql.cj.jdbc.Driver";
        String url="jdbc:mysql:///db1";
        String user="root";
        String password="123";
        Class.forName(className);
        Connection connection = DriverManager.getConnection(url, user, password);
        String sql="select * from stu";
        Statement statement = connection.createStatement();
        ResultSet resultSet = statement.executeQuery(sql);
        //next()方法，判断是否存在下一条数据，存在，返回true,并且会读出下一条数据
        while(resultSet.next()){
            //通过下标获取某列数据，JDBC下标是从1开始的
            System.out.println(resultSet.getInt(1));
            ///通过列名获取某列数据
            System.out.println(resultSet.getString("sname"));
            System.out.println(resultSet.getObject(3));
            System.out.println("-------------");
        }
        resultSet.close();
        ;
        statement.close();
        connection.close();
    }

    private static void test03() {
        Connection connection = null;
        Statement statement = null;
        try {
            Scanner scanner=new Scanner(System.in);
            System.out.println("输入你要修改学生的学号");
            int sid = scanner.nextInt();

            String className="com.mysql.cj.jdbc.Driver";
            String url="jdbc:mysql:///db1";
            String user="root";
            String password="123";
            Class.forName(className);
            connection = DriverManager.getConnection(url, user, password);
            String sql="delete from stu where sname="+sid;
            statement = connection.createStatement();
            int count = statement.executeUpdate(sql);
            System.out.println("受影响的行数="+count);
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            if(statement!=null){
                try {
                    statement.close();
                } catch (SQLException e) {
                    e.printStackTrace();
                }
            }
            if (connection!=null) {
                try {
                    connection.close();
                } catch (SQLException e) {
                    e.printStackTrace();
                }
            }
        }
    }

    private static void test02() throws ClassNotFoundException, SQLException {
        Scanner scanner=new Scanner(System.in);
        System.out.println("输入你要修改学生的学号");
        int sid = scanner.nextInt();

        String className="com.mysql.cj.jdbc.Driver";
        String url="jdbc:mysql:///db1";
        String user="root";
        String password="123";
        Class.forName(className);
        Connection connection = DriverManager.getConnection(url, user, password);
        String sql="delete from stu where sname="+sid;
        Statement statement = connection.createStatement();
        int count = statement.executeUpdate(sql);
        System.out.println("受影响的行数="+count);
        statement.close();
        connection.close();
    }

    private static void test01() throws ClassNotFoundException, SQLException {
        Scanner scanner=new Scanner(System.in);
        System.out.println("输入你要修改学生的学号");
        int sid = scanner.nextInt();
        System.out.println("输入你要修改学生的姓名");
        String sname = scanner.next();
        System.out.println("输入你要修改学生的年龄");
        int age = scanner.nextInt();

        String className="com.mysql.cj.jdbc.Driver";
        String url="jdbc:mysql:///db1";
        String user="root";
        String password="123";
        Class.forName(className);
        Connection connection = DriverManager.getConnection(url, user, password);
        String sql="update stu set sname='"+sname+"',age="+age+" where sid="+sid;
        Statement statement = connection.createStatement();
        int count = statement.executeUpdate(sql);
        System.out.println("受影响的行数="+count);
        statement.close();
        connection.close();
    }
}
